<HTML>
<HEAD><TITLE>Confirmation Page</TITLE><HEAD>
<BODY>
 <%-- Import the java.sql package --%>
            <%@ page import="java.sql.*"%>
            <%-- -------- Open Connection Code -------- --%>
            <%
            
            Connection conn = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            
            try {
                // Registering Postgresql JDBC driver with the DriverManager
                Class.forName("org.postgresql.Driver");

                // Open a connection to the database using DriverManager
                conn = DriverManager.getConnection(
                    "jdbc:postgresql://localhost/admissions?" +
                    "user=postgres&password=27428572");
            %>
			<%
			 // Begin transaction
			//insert Address
            conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			
			pstmt = conn.prepareStatement("INSERT INTO addresses (street, city, areaCode,"
					+ " zipCode, phoneno, state, teleCode) VALUES (?,?,?,?,?,?,?)");
   	        pstmt.setString(1, session.getAttribute("street").toString());
    	    pstmt.setString(2, session.getAttribute("city").toString());
			pstmt.setString(3, session.getAttribute("areaCode").toString());
			pstmt.setString(4, session.getAttribute("zipCode").toString());
			pstmt.setInt(5, Integer.parseInt(session.getAttribute("number").toString()));
			if(session.getAttribute("residence").equals("United States")) {
				String order = "SELECT id FROM states WHERE name = '"
					+session.getAttribute("state").toString()+"'";
				rs = stmt.executeQuery(order);
				rs.next();
				pstmt.setInt(6, rs.getInt(1));
				pstmt.setInt(7, 0);
			} else {
				String order = "SELECT id FROM states WHERE name = 'NA'";
				rs = stmt.executeQuery(order);
				rs.next();
				pstmt.setInt(6, rs.getInt(1));
				pstmt.setInt(7, Integer.parseInt(session.getAttribute("code").toString()));
			}
			pstmt.executeUpdate();

			

			//insert student
			pstmt = conn.prepareStatement("INSERT INTO student (name,address,spec,"
					+"residence,citizenship) VALUES (?,?,?,?,?)");
			pstmt.setString(1, session.getAttribute("fullName").toString());
			String order = "SELECT id FROM addresses WHERE street = '"+session.getAttribute("street").toString()+"'";
			rs = stmt.executeQuery(order);
			rs.next();
			pstmt.setInt(2,rs.getInt(1));
			order = "SELECT id FROM specializations WHERE name = '"+session.getAttribute("specialization").toString()+"'";
			rs = stmt.executeQuery(order);
			rs.next();
			pstmt.setInt(3,rs.getInt(1));
			order = "SELECT id FROM countries WHERE name = '"+session.getAttribute("residence").toString()+"'";
			rs = stmt.executeQuery(order);
			rs.next();
			pstmt.setInt(4,rs.getInt(1));
			order = "SELECT id FROM countries WHERE name = '"+session.getAttribute("country").toString()+"'";
			rs = stmt.executeQuery(order);
			rs.next();
			pstmt.setInt(5,rs.getInt(1));
			pstmt.executeUpdate();

			//insert degrees
			String sCount = (String)session.getAttribute("count");
			int count = Integer.parseInt(sCount);
			for(int i = 0; i<count;i++)
			{
			String stringCount = Integer.toString(i+1);
			if(session.getAttribute("discipline"+stringCount) != null)
			{
				rs = stmt.executeQuery("SELECT id FROM disciplines WHERE name ='"+session.getAttribute("discipline"+stringCount).toString()+"'");
				if(!rs.next())
				{
					pstmt = conn.prepareStatement("INSERT INTO disciplines (name) VALUEs (?)");
					pstmt.setString(1, session.getAttribute("discipline"+stringCount).toString());
					pstmt.executeUpdate();
				}
			}
			pstmt = conn.prepareStatement("INSERT INTO degrees"
					+" (name,location,university,gpa,date,title,student) VALUES (?,?,?,?,?,?,?)");
			order = "SELECT id FROM disciplines WHERE name = '"
				+session.getAttribute("discipline"+stringCount).toString()+"'";
			rs = stmt.executeQuery(order);
			rs.next();
			pstmt.setInt(1,rs.getInt(1));
			order = "SELECT id FROM states WHERE name = '"+session.getAttribute("location"+stringCount).toString()+"'";
			rs = stmt.executeQuery(order);
			rs.next();
			pstmt.setInt(2,rs.getInt(1));
			order = "SELECT id FROM universities WHERE name = '"+session.getAttribute("universities"+stringCount).toString()+"'";
			rs = stmt.executeQuery(order);
			rs.next();
			pstmt.setInt(3,rs.getInt(1));
			pstmt.setString(4,session.getAttribute("gpa"+stringCount).toString());
			String date = session.getAttribute("awardedMonth"+stringCount).toString() + session.getAttribute("awardedYear"+stringCount).toString();
			pstmt.setString(5,date);
			pstmt.setString(6,session.getAttribute("title"+stringCount).toString());
			order = "SELECT id FROM student WHERE name = '"+session.getAttribute("fullName").toString()+"'";
			rs = stmt.executeQuery(order);
			rs.next();
			int studentId = rs.getInt(1);
			pstmt.setInt(7,rs.getInt(1));
			pstmt.executeUpdate();
			order = "SELECT user_id FROM users WHERE user_name ='"+session.getAttribute("userName").toString()+"'";
			rs = stmt.executeQuery(order);
			rs.next();
			int userId = rs.getInt(1);
			order = "UPDATE user_status SET user_status='TRUE' WHERE user_name='"+session.getAttribute("userName")+"'";
			pstmt = conn.prepareStatement(order);
			pstmt.executeUpdate();
			order = "INSERT INTO student_status (user_id, student_status) VALUES (?,?)";
			pstmt = conn.prepareStatement(order);
			pstmt.setInt(1,userId);
			pstmt.setString(2,"Pending");
			pstmt.executeUpdate();
			order = "INSERT INTO user_link (user_id, student_id)VALUES (?,?)";
			pstmt.setInt(1,userId);
			pstmt.setInt(2,studentId);
			pstmt = conn.prepareStatement(order);
			pstmt.executeUpdate();
			
			}

			conn.setAutoCommit(true);
       }

			catch (SQLException e){
				throw new RuntimeException(e);
			}

finally{
		if(rs != null){
					try{ 
						rs.close();
					}catch (SQLException e){ }
					rs = null;
				}
				if(pstmt != null){
					try{
						pstmt.close();
					}catch(SQLException e){}
					pstmt = null;
				}
				if(conn != null){
					try{
						conn.close();
					}catch (SQLException e) { }
					conn = null;
				}

		}
            %>
Your Application has been sent sucessfully.

</BODY>
</HTML>
